DROP TABLE KOMENTARZE_ROSLINY;
DROP TABLE KOMENTARZE_SZKODNIKI;
DROP TABLE ZDJECIA;
DROP TABLE KOMENTARZE_PESTYCYDY;
DROP TABLE UZYTKOWNIK_ROSLINA CASCADE;
DROP TABLE UZYTKOWNICY CASCADE;
DROP TABLE CHOROBY_ROSLIN CASCADE;
DROP TABLE SZKODNIKI_I_PESTYCYDY CASCADE;
DROP TABLE PESTYCYDY CASCADE;
DROP TABLE CHOROBY_I_SZKODNIKI CASCADE;
DROP TABLE SASIEDZTWO CASCADE;
DROP TABLE MOZLIWOSCI_ROSLINY CASCADE;
DROP TABLE MOZLIWOSCI CASCADE;
DROP TABLE ROSLINY CASCADE;

CREATE TABLE ZDJECIA(
	id SERIAL PRIMARY KEY
);

CREATE TABLE ROSLINY(
	id SERIAL PRIMARY KEY,
	nazwa VARCHAR NOT NULL,
	nazwa_lacinska VARCHAR,
	cechy TEXT, --trujace
	prace TEXT, --w lecie nawozimy
	kwitniecie TEXT,
	zdjecie VARCHAR
);

INSERT INTO ROSLINY(nazwa, nazwa_lacinska, cechy, prace, kwitniecie, zdjecie) VALUES('róża czerwona', 'Rosaceae', 'Pachnąca', 'Przycinać latem', '30 czerwca - 20 lipca', NULL), ('Kaktus', 'Echinocactus cinnabarinus', 'Kłująca', 'Podlewać co 6 miesięcy', null, 'http://i.obrazky.pl/kot-kaktus-807-OBRAZKY.PL.jpg');

CREATE TABLE MOZLIWOSCI(
	id SERIAL PRIMARY KEY,
	opis TEXT --przepuszczalne podloze, 30-45cm odstepu
);

INSERT INTO MOZLIWOSCI(opis) VALUES('Odstep < 15'), ('Odstep 15-30'), ('Odstep 30-45'), ('Odstep > 45'), ('Cien'), ('Polcien'), ('Slonce'), ('Gleba kwasna'), ('Gleba zasadowa'), ('Rabaty kwiatowe'), ('Kwiaty ciete');

CREATE RULE mozliwosci_protect AS ON INSERT TO mozliwosci DO INSTEAD NOTHING;

CREATE TABLE MOZLIWOSCI_ROSLINY(
	idr INTEGER REFERENCES ROSLINY ON DELETE CASCADE NOT NULL,
	idm INTEGER REFERENCES MOZLIWOSCI ON DELETE CASCADE NOT NULL,
	UNIQUE(idr, idm)
);

CREATE TABLE SASIEDZTWO(
	ida INTEGER REFERENCES ROSLINY NOT NULL,
	idb INTEGER REFERENCES ROSLINY NOT NULL
);

CREATE OR REPLACE FUNCTION sasiedztwo_check() RETURNS trigger AS $sasiedztwo_check$
BEGIN
	IF (SELECT COUNT(*) FROM SASIEDZTWO WHERE (ida = NEW.ida AND idb = NEW.idb) OR (ida = NEW.idb AND idb = NEW.ida)) != 0 THEN
		RETURN NULL;
	END IF;
	RETURN NEW;
END;
$sasiedztwo_check$ LANGUAGE plpgsql;

CREATE TRIGGER sasiedztwo_check BEFORE INSERT OR UPDATE ON sasiedztwo
FOR EACH ROW EXECUTE PROCEDURE sasiedztwo_check();

CREATE TABLE CHOROBY_I_SZKODNIKI(
	id SERIAL PRIMARY KEY,
	nazwa VARCHAR NOT NULL,
	opis TEXT
);

INSERT INTO CHOROBY_I_SZKODNIKI (nazwa, opis) VALUES('Glizda', 'Pożera liście');

CREATE TABLE PESTYCYDY(
	id SERIAL PRIMARY KEY,
	nazwa VARCHAR NOT NULL,
	opis TEXT
);

INSERT INTO PESTYCYDY (nazwa, opis) VALUES('Kwas siarkowy', 'Zabija każdego szkodnika, posiada zauważalne skutki uboczne dla roślin');

CREATE TABLE SZKODNIKI_I_PESTYCYDY(
	ids INTEGER REFERENCES CHOROBY_I_SZKODNIKI ON DELETE CASCADE NOT NULL,
	idp INTEGER REFERENCES PESTYCYDY ON DELETE CASCADE NOT NULL,
	UNIQUE(ids, idp)
);

CREATE TABLE CHOROBY_ROSLIN(
	idr INTEGER REFERENCES ROSLINY ON DELETE CASCADE NOT NULL,
	idc INTEGER REFERENCES choroby_i_szkodniki ON DELETE CASCADE NOT NULL,
	UNIQUE(idr, idc)
);


CREATE TABLE UZYTKOWNICY(
	id SERIAL PRIMARY KEY,
	login VARCHAR NOT NULL,
	pass CHAR(32) NOT NULL,
	e_mail VARCHAR,
	superuser BOOLEAN
);

INSERT INTO UZYTKOWNICY (login, pass, e_mail, superuser) VALUES ('Maciek', '912ec803b2ce49e4a541068d495ab570', 'mac.eek@gmail.com', true), ('Grzesiek', '05329126b01a191c87f243e7473ac011', 'grzegorz.guspiel@gmail.com', true);

CREATE TABLE UZYTKOWNIK_ROSLINA(
	idu INTEGER REFERENCES UZYTKOWNICY ON DELETE CASCADE NOT NULL,
	idr INTEGER REFERENCES ROSLINY ON DELETE CASCADE NOT NULL,
	UNIQUE(idu, idr)
);


CREATE TABLE KOMENTARZE_ROSLINY(
	idu INTEGER REFERENCES UZYTKOWNICY ON DELETE CASCADE NOT NULL,
	idr INTEGER REFERENCES ROSLINY ON DELETE CASCADE NOT NULL,
	tresc TEXT NOT NULL,
	czas TIMESTAMP NOT NULL
);

CREATE TABLE KOMENTARZE_SZKODNIKI(
	idu INTEGER REFERENCES UZYTKOWNICY ON DELETE CASCADE NOT NULL,
	idr INTEGER REFERENCES CHOROBY_I_SZKODNIKI ON DELETE CASCADE NOT NULL,
	tresc TEXT NOT NULL,
	czas TIMESTAMP NOT NULL
);

CREATE TABLE KOMENTARZE_PESTYCYDY(
	idu INTEGER REFERENCES UZYTKOWNICY ON DELETE CASCADE NOT NULL,
	idr INTEGER REFERENCES PESTYCYDY ON DELETE CASCADE NOT NULL,
	tresc TEXT NOT NULL,
	czas TIMESTAMP NOT NULL
);


--FILTROWANIE

--SELECT idr FROM T
--WHERE idT IN (setof filters)
--GROUP BY idr
--HAVING COUNT(*) = setof filters.size;
